Previous Topic

Next Topic

Example Duplicate Record Check Formulas

The following CP Duplicate Record Check Formula and Duplicate Record Check Formula specify a duplicate match key using the first three digits/characters of the zip code, the first four characters of the last name (if one exists), the first character of the first name, or if the customer did not have a last name, the first five characters of the company name:

CP Duplicate Record Check Formula

UPPER(SUBSTRING(Name.ZIP,1,3) + (CASE WHEN Name.LAST_NAME<>'' THEN (SUBSTRING(Name.LAST_NAME,1,4) + SUBSTRING(Name.FIRST_NAME,1,1)) ELSE SUBSTRING(Name.COMPANY,1,5) END))

Duplicate Record Check Formula

upp(con(mid(Name.ZIP,1,3),pick(Name.LAST_NAME<>'',mid(Name.COMPANY,1,5),con(mid(Name.LAST_NAME,1,4),mid(Name.FIRST_NAME,1,1)))))

For customer Diane Block with an address of 500 Main Street, Austin, Texas 78708, the following duplicate match key would be created:

787BLOCD

See Also

Checking for Duplicate Records


ASI logo 10.6 Production Release. Updated 1/20/2006 12:52:05 PM
© Copyright by Advanced Solutions International, Inc. All rights reserved.